//////////////////////////////////////////////////////////////////////////
// FileName:SysFunctions
// CreateBy:DragonXiong
// CreateDate:2009-5-4
// Description:
//		Class SysFunctions,table _SysFunctions
//      
// Modify Log:090531:Check Path Empty
//            090609:Format the parameter of function --dragon
//            090611:Format the SQL Keywords, and Remove the default the User Functions --dragon
//            090615:Add some comment,add globalMessage --dragon
//            090616:Modify DB columns name to Model.SystemFunctions.xxx --dragon
//            090731:Add GetFuncsAndOperate Method --fenky
//            090820:Modify the Lenght & DbType of Column --dragon
//            20100113:Add column IsMenu --DragonXiong
//	
// Copyright:ASL-MIS
/////////////////////////////////////////////////////////////////////////
using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

using Auto.Data.DBUtility;
using Auto.Components;
namespace Auto.Data.Systems.DAL
{
    /// <summary>
    /// SystemFunctions for Menu
    /// </summary>
    public class SysFunctions
    {

        #region --New--
        ///// <summary>
        ///// Selects the specified func ID.
        ///// </summary>
        ///// <param name="funcID">The func ID.</param>
        ///// <param name="sysID">The sys ID.</param>
        ///// <returns>Model.SysFunctions</returns>
        //public Model.SysFunctions GetModel(string sysID, string funcID, string Culture)
        //{
        //    Model.SysFunctions sysFunctions = new Model.SysFunctions();
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("SELECT RecID,FuncID,SysID,CurtureInfo,ParentID,FuncName,URL,Target,Icon,Path,IsEnd FROM _SysFunctions ");
        //    strSql.Append(" WHERE FuncID='" + funcID + "' AND SysID='" + sysID + "' AND CultrueInfo='" + Culture + "'");
        //    DataTable dt = new DataTable();
        //    try
        //    {
        //        dt = DbHelper.ExecuteQuery(strSql.ToString()).Tables[0];
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    if (dt.Rows.Count > 0)
        //    {
        //        DataRow dr = dt.Rows[0];
        //        sysFunctions.RecID = StrHelper.GetIntValue(dr["RecID"]);// int.Parse(dr["RecID"].ToString());

        //        sysFunctions.FuncID = StrHelper.GetStringValue(dr["FuncID"]);// == DBNull.Value ? "" : dr["FuncID"].ToString();
        //        sysFunctions.SysID = StrHelper.GetStringValue(dr["SysID"]);//== DBNull.Value ? "" : dr["SysID"].ToString();
        //        sysFunctions.ParentID = StrHelper.GetStringValue(dr["ParentID"]);// == DBNull.Value ? "" : dr["ParentID"].ToString();
        //        sysFunctions.FuncName = StrHelper.GetStringValue(dr["FuncName"]);// == DBNull.Value ? "" : dr["FuncName"].ToString();
        //        sysFunctions.URL = StrHelper.GetStringValue(dr["URL"]);// == DBNull.Value ? "" : dr["URL"].ToString();
        //        sysFunctions.Target = StrHelper.GetStringValue(dr["Target"]);// == DBNull.Value ? "" : dr["Target"].ToString();
        //        sysFunctions.Icon = StrHelper.GetStringValue(dr["Icon"]);// == DBNull.Value ? "" : dr["Icon"].ToString();
        //        sysFunctions.Path = StrHelper.GetStringValue(dr["Path"]);// == DBNull.Value ? "" : dr["Path"].ToString();
        //        sysFunctions.IsEnd = StrHelper.GetBoolValue(dr["IsEnd"]);
        //    }
        //    return sysFunctions;
        //}

        /// <summary>
        /// Get Function Model
        /// </summary>
        /// <param name="cultureInfo">Culture Info</param>
        /// <param name="sysID">System ID</param>
        /// <param name="funcID">Function ID</param>
        /// <returns>Model Function</returns>
        public Model.SysFunctions GetModel(string cultureInfo, string sysID, string funcID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT TOP 1 RecID,CultureInfo,SysID,FuncID,ParentID,FuncName,URL,Target,Icon,Path,IsEnd,IsMenu FROM _SysFunctions ");
            strSql.Append(" WHERE CultureInfo=@CultureInfo and SysID=@SysID AND FuncID=@FuncID ");
            SqlParameter[] parameters = {
					new SqlParameter("@CultureInfo", SqlDbType.VarChar,10),
					new SqlParameter("@SysID", SqlDbType.VarChar,20),
					new SqlParameter("@FuncID", SqlDbType.VarChar,20)};
            parameters[0].Value = cultureInfo;
            parameters[1].Value = sysID;
            parameters[2].Value = funcID;

            Model.SysFunctions model = new Model.SysFunctions();
            DataSet ds = DbHelper.ExecuteQuery(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                //if (ds.Tables[0].Rows[0]["RecID"].ToString() != "")
                //{
                //    model.RecID = int.Parse(ds.Tables[0].Rows[0]["RecID"].ToString());
                //}
                //model.CultureInfo = ds.Tables[0].Rows[0]["CultureInfo"].ToString();
                //model.SysID = ds.Tables[0].Rows[0]["SysID"].ToString();
                //model.FuncID = ds.Tables[0].Rows[0]["FuncID"].ToString();
                //model.ParentID = ds.Tables[0].Rows[0]["ParentID"].ToString();
                //model.FuncName = ds.Tables[0].Rows[0]["FuncName"].ToString();
                //model.URL = ds.Tables[0].Rows[0]["URL"].ToString();
                //model.Target = ds.Tables[0].Rows[0]["Target"].ToString();
                //model.Icon = ds.Tables[0].Rows[0]["Icon"].ToString();
                //model.Path = ds.Tables[0].Rows[0]["Path"].ToString();
                //if (ds.Tables[0].Rows[0]["IsEnd"].ToString() != "")
                //{
                //    if ((ds.Tables[0].Rows[0]["IsEnd"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsEnd"].ToString().ToLower() == "true"))
                //    {
                //        model.IsEnd = true;
                //    }
                //    else
                //    {
                //        model.IsEnd = false;
                //    }
                //}
                DataRow dr =ds.Tables[0].Rows[0];
                model.RecID = StrHelper.GetIntValue(dr[Model.SysFunctions.FIELD_RecID]);// int.Parse(dr["RecID"].ToString());

                model.FuncID = StrHelper.GetStringValue(dr[Model.SysFunctions.FIELD_FuncID]);// == DBNull.Value ? "" : dr["FuncID"].ToString();
                model.SysID = StrHelper.GetStringValue(dr[Model.SysFunctions.FIELD_SysID]);//== DBNull.Value ? "" : dr["SysID"].ToString();
                model.ParentID = StrHelper.GetStringValue(dr[Model.SysFunctions.FIELD_ParentID]);// == DBNull.Value ? "" : dr["ParentID"].ToString();
                model.FuncName = StrHelper.GetStringValue(dr[Model.SysFunctions.FIELD_FuncName]);// == DBNull.Value ? "" : dr["FuncName"].ToString();
                model.URL = StrHelper.GetStringValue(dr[Model.SysFunctions.FIELD_URL]);// == DBNull.Value ? "" : dr["URL"].ToString();
                model.Target = StrHelper.GetStringValue(dr[Model.SysFunctions.FIELD_Target]);// == DBNull.Value ? "" : dr["Target"].ToString();
                model.Icon = StrHelper.GetStringValue(dr[Model.SysFunctions.FIELD_Icon]);// == DBNull.Value ? "" : dr["Icon"].ToString();
                model.Path = StrHelper.GetStringValue(dr[Model.SysFunctions.FIELD_Path]);// == DBNull.Value ? "" : dr["Path"].ToString();
                model.IsEnd = StrHelper.GetBoolValue(dr[Model.SysFunctions.FIELD_IsEnd]);
                model.IsMenu = StrHelper.GetBoolValue(dr[Model.SysFunctions.FIELD_IsMenu]);
                return model;
            }
            else
            {
                return null;
            }
        }

        #endregion --New--


        #region --Operate--
        //public bool Exists(string funcID, string sysID,string Cultrue)
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("SELECT count(1) FROM _SysFunctions");
        //    strSql.Append(" WHERE FuncID='" + funcID + "' and SysID='" + sysID + "' AND CultureInfo='"+Cultrue+"'");
        //    try
        //    {
        //        return DbHelper.Exists(strSql.ToString());
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //}

        /// <summary>
        /// System function exists or not
        /// </summary>
        /// <param name="cultureInfo">Culture Info</param>
        /// <param name="sysID">System ID</param>
        /// <param name="funcID">Function ID</param>
        /// <returns>System function exists or not</returns>
        public bool Exists(string cultureInfo, string sysID, string funcID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT COUNT(1) FROM _SysFunctions");
            strSql.Append(" WHERE CultureInfo=@CultureInfo and SysID=@SysID AND FuncID=@FuncID ");
            try
            {
                SqlParameter[] parameters = {
					new SqlParameter("@CultureInfo", SqlDbType.VarChar,10),
					new SqlParameter("@SysID", SqlDbType.VarChar,20),
					new SqlParameter("@FuncID", SqlDbType.VarChar,20)};
                parameters[0].Value = cultureInfo;
                parameters[1].Value = sysID;
                parameters[2].Value = funcID;

                return DbHelper.Exists(strSql.ToString(), parameters);
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
        #endregion --Operate--

        #region --SQL--
        ///// <summary>
        ///// Inserts this instance.
        ///// </summary>
//        public bool Insert(Model.SysFunctions sysFunctions)
//        {
//            int result;
//            string sql = "";
//            try
//            {
//                sql = @"INSERT INTO _SysFunctions(FuncID,ParentID,SysID,FuncName,URL,Target,Icon,Path,IsEnd) 
//                                    VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')";
//                sql = string.Format(sql, sysFunctions.FuncID, sysFunctions.ParentID, sysFunctions.SysID,
//                              sysFunctions.FuncName, sysFunctions.URL, sysFunctions.Target,
//                              sysFunctions.Icon, sysFunctions.Path, sysFunctions.IsEnd);
//                result = DbHelper.ExecuteNonQuery(sql);
//                if (result == 0)
//                {
//                    throw new Exception("Error When Insert Data To SysFunctions!");
//                }
//                return true;
//            }
//            catch (Exception ex)
//            {
//                throw (ex);
//            }

//        }

        /// <summary>
        /// Insert System Functions
        /// </summary>
        /// <param name="model">Model SysFunctions</param>
        /// <returns>insert success or not</returns>
        public bool Insert(Model.SysFunctions model)
        {
            int result = 0;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO _SysFunctions(");
            strSql.Append("CultureInfo,SysID,FuncID,ParentID,FuncName,URL,Target,Icon,Path,IsEnd,IsMenu)");
            strSql.Append(" VALUES (");
            strSql.Append("@CultureInfo,@SysID,@FuncID,@ParentID,@FuncName,@URL,@Target,@Icon,@Path,@IsEnd,@IsMenu)");
            try
            {
                SqlParameter[] parameters = {
					new SqlParameter("@CultureInfo", SqlDbType.VarChar,10),
					new SqlParameter("@SysID", SqlDbType.VarChar,20),
					new SqlParameter("@FuncID", SqlDbType.VarChar,20),
					new SqlParameter("@ParentID", SqlDbType.VarChar,20),
					new SqlParameter("@FuncName", SqlDbType.NVarChar,30),
					new SqlParameter("@URL", SqlDbType.NVarChar,100),
					new SqlParameter("@Target", SqlDbType.NVarChar,10),
					new SqlParameter("@Icon", SqlDbType.NVarChar,20),
					new SqlParameter("@Path", SqlDbType.NVarChar,200),
					new SqlParameter("@IsEnd", SqlDbType.Bit,1),
                    new SqlParameter("@IsMenu", SqlDbType.Bit,1)};
                parameters[0].Value = model.CultureInfo;
                parameters[1].Value = model.SysID;
                parameters[2].Value = model.FuncID;
                parameters[3].Value = model.ParentID;
                parameters[4].Value = model.FuncName;
                parameters[5].Value = model.URL;
                parameters[6].Value = model.Target;
                parameters[7].Value = model.Icon;
                if (!string.IsNullOrEmpty(model.ParentID))//To check empty. Modify by Dragon 090531
                {
                    string path = GetModel(model.CultureInfo, model.SysID, model.ParentID).Path;
                    if(string.IsNullOrEmpty(path))
                    {
                        parameters[8].Value = model.ParentID; //model.Path;
                    }
                    else
                    {
                        parameters[8].Value = path + "." + model.ParentID; //model.Path;
                    }

                }
                else
                {
                    parameters[8].Value = "";
                }
                parameters[9].Value = model.IsEnd;
                parameters[10].Value = model.IsMenu;

                result= DbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
                if (result == 0)
                {
                    throw new Exception(Resources.GetResource(GlobalResources.GlobalMessage.InsertError,this.GetType()));
                }
                else
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        ///// <summary>
        ///// Delete Record
        ///// </summary>
        ///// <param name="funcID">Function ID</param>
        ///// <param name="sysID">System ID</param>
        ///// <returns>delete success or not</returns>
        //public bool Delete(string funcID, string sysID)
        //{
        //    string sql = "DELETE FROM _SysFunctions WHERE 1=1";
        //    if (sysID != "")
        //    {
        //        sql += " AND SysID=' " + sysID + "'";
        //    }
        //    if (funcID != "")
        //    {
        //        sql += "AND FuncID='" + funcID + "'";
        //    }
        //    try
        //    {
        //        return DbHelper.ExecuteNonQuery(sql) > 0;
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //}

        /// <summary>
        /// Delete Functions By Key
        /// </summary>
        /// <param name="cultureInfo">Culture Info</param>
        /// <param name="sysID">System ID</param>
        /// <param name="funcID">Function ID</param>
        /// <returns>delete success or not</returns>
        public bool Delete(string cultureInfo, string sysID, string funcID)
        {
            int result = 0;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("DELETE FROM _SysFunctions ");
            strSql.Append(" WHERE 1=1  ");
            if (!string.IsNullOrEmpty(cultureInfo))
            {
                strSql.Append(" AND CultureInfo=@CultureInfo");
            }
            if (!string.IsNullOrEmpty(sysID))
            {
                strSql.Append(" AND SysID=@SysID");
            }
            if (!string.IsNullOrEmpty(funcID))
            {
                strSql.Append(" AND FuncID=@FuncID");
            }

            try
            {
                SqlParameter[] parameters = {
					new SqlParameter("@CultureInfo", SqlDbType.VarChar,10),
					new SqlParameter("@SysID", SqlDbType.NVarChar,20),
					new SqlParameter("@FuncID", SqlDbType.NVarChar,20)};
                parameters[0].Value = cultureInfo;
                parameters[1].Value = sysID;
                parameters[2].Value = funcID;

                result = DbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
                //if (result == 0)
                //{
                //    throw new Exception("Error When Delete '"+funcID+"'");
                //}
                //else
                //{
                    return true;
                //}
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        #endregion --SQL--


        #region --GetDate--
        /// <summary>
        /// Get System Functions by Key
        /// </summary>
        /// <param name="cultrueInfo">Culture Info</param>
        /// <param name="sysID">System ID</param>
        /// <param name="funcID">Function ID</param>
        /// <returns>DataTable Functions</returns>
        public DataTable GetDataByKeyValue(string cultrueInfo, string sysID,  string funcID)
        {
            StringBuilder sql = new StringBuilder("SELECT FuncID,ParentID,SysID,FuncName,URL,Target,Icon,Path,IsEnd,IsMenu FROM _SysFunctions WHERE 1=1");
            if (!string.IsNullOrEmpty(sysID))
            {
                sql.AppendFormat(" AND SysID='{0}'", sysID);
            }
            if (!string.IsNullOrEmpty(cultrueInfo))
            {
                sql.AppendFormat(" AND CultureInfo='{0}'", cultrueInfo);
            }
            if (!string.IsNullOrEmpty(funcID))
            {
                sql.AppendFormat(" AND FuncID='{0}'", funcID);
            }
            try
            {
                return DbHelper.ExecuteQuery(sql.ToString()).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// Get Root Function
        /// </summary>
        /// <param name="cultrueInfo">Culture Info</param>
        /// <param name="sysID">System ID</param>
        /// <returns>Data Table Root Functions</returns>
        public DataTable GetSysFuntionsTop(string cultrueInfo, string sysID)
        {


//            StringBuilder sql = new StringBuilder(@" SELECT FuncID,ParentID,SysID,FuncName,URL,Target,Icon,Path,IsEnd 
//                                                    FROM _SysFunctions 
//                                                    WHERE SysID='User' AND (ParentID IS NULL or ParentID='')");
//            if (!string.IsNullOrEmpty(cultrueInfo))
//            {
//                sql.AppendFormat(" AND CultureInfo='{0}'", cultrueInfo);
//            }           
//            sql.Append(" UNION ALL");
            StringBuilder sql = new StringBuilder();
            sql.Append(@" SELECT FuncID,ParentID,SysID,FuncName,URL,Target,Icon,Path,IsEnd,IsMenu 
                                                    FROM _SysFunctions 
                                                    WHERE 1=1 AND (ParentID IS NULL or ParentID='')");
            if (!string.IsNullOrEmpty(sysID))
            {
                sql.AppendFormat(" AND SysID='{0}'", sysID);
            }
            if (!string.IsNullOrEmpty(cultrueInfo))
            {
                sql.AppendFormat(" AND CultureInfo='{0}'", cultrueInfo);
            }

            try
            {
                return DbHelper.ExecuteQuery(sql.ToString()).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// Get Functions Without Root Functions
        /// </summary>
        /// <param name="cultrueInfo">Culture Info</param>
        /// <param name="sysID">System ID</param>
        /// <returns>DataTable Functions Without Root Functions</returns>
        public DataTable GetSysFuntionsWithOutTop(string cultrueInfo, string sysID)
        {


//            StringBuilder sql = new StringBuilder(@" SELECT FuncID,ParentID,SysID,FuncName,URL,Target,Icon,Path,IsEnd 
//                                                    FROM _SysFunctions 
//                                                    WHERE SysID='User' AND (ParentID IS NOT NULL or ParentID!='')");
//            if (!string.IsNullOrEmpty(cultrueInfo))
//            {
//                sql.AppendFormat(" AND CultureInfo='{0}'", cultrueInfo);
//            }
//            sql.Append(" UNION ALL");
            StringBuilder sql = new StringBuilder();
            sql.Append(@" SELECT FuncID,ParentID,SysID,FuncName,URL,Target,Icon,Path,IsEnd,IsMenu 
                                                    FROM _SysFunctions 
                                                    WHERE 1=1 AND (ParentID IS NOT NULL or ParentID!='')");
            if (!string.IsNullOrEmpty(sysID))
            {
                sql.AppendFormat(" AND SysID='{0}'", sysID);
            }
            if (!string.IsNullOrEmpty(cultrueInfo))
            {
                sql.AppendFormat(" AND CultureInfo='{0}'", cultrueInfo);
            }

            try
            {
                return DbHelper.ExecuteQuery(sql.ToString()).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// Get Functions By Parent Function ID
        /// </summary>
        /// <param name="cultrueInfo">Culture Info</param>
        /// <param name="sysID">System ID</param>
        /// <param name="parentID">Parent ID</param>
        /// <returns>DataTable Functions</returns>
        public DataTable GetDataByParentID(string cultrueInfo, string sysID, string parentID)
        {
            StringBuilder sql = new StringBuilder("SELECT FuncID,ParentID,SysID,FuncName,URL,Target,Icon,Path,IsEnd,IsMenu FROM _SysFunctions WHERE 1=1");
            if (!string.IsNullOrEmpty(sysID))
            {
                sql.AppendFormat(" AND SysID='{0}'", sysID);
            }
            if (!string.IsNullOrEmpty(parentID))
            {
                sql.AppendFormat(" AND ParentID='{0}'", parentID);
            }
            if (!string.IsNullOrEmpty(parentID))
            {
                sql.AppendFormat(" AND CultureInfo='{0}'", cultrueInfo);
            }
            try
            {
                return DbHelper.ExecuteQuery(sql.ToString()).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// Get List
        /// </summary>
        /// <param name="strWhere">field1 (=,LIKE...) '' (AND/OR..)  fields2 (=,LIKE...) '' ... </param>
        /// <returns>DataTable Functions</returns>
        public DataTable GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT RecID,CultureInfo,SysID,FuncID,ParentID,FuncName,URL,Target,Icon,Path,IsEnd,IsMenu ");
            strSql.Append(" FROM _SysFunctions ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" WHERE " + strWhere);
            }
            return DbHelper.ExecuteQuery(strSql.ToString()).Tables[0];
        }


        /// <summary>
        /// Gets the funcs and operate.
        /// </summary>
        /// <param name="cultureInfo">The culture info.</param>
        /// <param name="sysID">The sys ID.</param>
        /// <param name="funcID">The func ID.</param>
        /// <returns>DataTable Functions</returns>
        public DataTable GetFuncsAndOperate(string cultureInfo,string sysID,string funcID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT funcs.FuncID,funcs.FuncName,funcs.SysID,operate.OperateID
                            FROM _SysFunctions funcs
                            LEFT JOIN _SysFuncOperate operate ON operate.SysID = funcs.SysID AND operate.FuncID = funcs.FuncID
                            WHERE URL!='' ");
            if (!string.IsNullOrEmpty(cultureInfo))
            {
                strSql.AppendFormat(" AND CultureInfo='{0}'", cultureInfo);
            }

            if (!string.IsNullOrEmpty(sysID))
            {
                strSql.AppendFormat(" AND funcs.SysID='{0}'", sysID);
            }

            if (!string.IsNullOrEmpty(funcID))
            {
                strSql.AppendFormat(" AND (Path LIKE '%{0}%' OR funcs.FuncID='{0}' OR funcs.SysID='{0}')", funcID);
            }

            return DbHelper.ExecuteQuery(strSql.ToString()).Tables[0];
        }

        #endregion --GetData--

    }
}
